import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Iterator;

import net.didion.jwnl.JWNL;
import net.didion.jwnl.JWNLException;
import net.didion.jwnl.data.POS;
import net.didion.jwnl.data.Pointer;
import net.didion.jwnl.data.Synset;
import net.didion.jwnl.data.Word;
import net.didion.jwnl.dictionary.Dictionary;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;



//convert wordnet info to mysql
public class Converter {
	Connection conn = null;
	Statement stmt = null;

	
	public int initialize() {
		//change the jwnl configure file path here
		String propsFile = "D:\\eclipse\\plugins\\own_added_plugins\\jwnl14-rc2\\config\\file_properties.xml";
		try {
			JWNL.initialize(new FileInputStream(propsFile));
		} catch (FileNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			System.out.println("lhdgriver says : JWNL properties file can not be found!");
			return 1;
		} catch (JWNLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			System.out.println("lhdgriver says : JWNL can not be initialized!");
			return 2;
		}
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("lhdgriver says : jdbc can not be connected!");
			return 3;
		}
		try {
			conn = (Connection) DriverManager
					.getConnection("jdbc:mysql://localhost/weight_wordnet?"
							+ "user=root&password=890911");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("lhdgriver says : mysql can not be connected! Check host/username/password");
			return 4;
		}
		try {
			stmt = (Statement) conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("lhdgriver says : statement can not be created!");
			return 5;
		}
		
		return 0;
	}
	
	public int close() {
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("lhdgriver says : connection can't be closed");
			return 1;
		}
		JWNL.shutdown();
		return 0;
	}
	
	private int insertRelatedSynset() throws JWNLException, SQLException {
		Iterator iter = null;
		Synset s = null;
		StringBuffer buffered_query = new StringBuffer();
		String query = null;
		String synset_off_off = null;
		
		iter = Dictionary.getInstance().getSynsetIterator(POS.NOUN);
		//store 'synsetID#synsetID'
		HashSet<String> dupHash = new HashSet<String>();
		
		while(iter.hasNext()) {			
			s = (Synset)iter.next();
			long a = s.getOffset();
			Pointer p[] = s.getPointers();
			for(Pointer i : p) {
				if (i.getTargetPOS() == POS.NOUN || i.getTargetPOS() == POS.VERB) {
					Synset x = i.getTargetSynset();
					long b = x.getOffset();
					if (a < b) 
						synset_off_off = String.valueOf(a) + "#" + String.valueOf(b);
					else
						synset_off_off = String.valueOf(b) + "#" + String.valueOf(a);
					if (dupHash.contains(synset_off_off)) continue;
					dupHash.add(synset_off_off);
				}
			}
		}
		
		iter = dupHash.iterator();
		buffered_query.append("insert into weight_wordnet.related_synset (syn_off_small, syn_off_big) values ");
		int num = 0;
		while(iter.hasNext()) {
			num++;
			if (num % 2000 == 0) {
				query = buffered_query.toString();
				query = query.substring(0, query.length() - 1);
				stmt.execute(query);
				buffered_query.delete(0, buffered_query.length());
				buffered_query.append("insert into weight_wordnet.related_synset (syn_off_small, syn_off_big) values ");
			}
			synset_off_off = (String)iter.next();
			String ss[] = synset_off_off.split("#");
			buffered_query.append("(" + ss[0] + "," + ss[1] + "),");
		}
		query = buffered_query.toString();
		query = query.substring(0, query.length() - 1);
		stmt.execute(query);
		return 0;
	}
	//fill word synset_off in synset_word_gnum, this function is used in insertWordNetInfo only
	//x is either POS.NOUN or POS.VERB
	private int insertSynsetWordGnum(POS x) throws SQLException {
		Iterator iter = null;
		Synset s = null;
		StringBuffer buffered_query = new StringBuffer();
		int word_type = 0;
		if (x == POS.NOUN) word_type = 1;
		if (x == POS.VERB) word_type = 2;
		try {
			iter = Dictionary.getInstance().getSynsetIterator(x);
		} catch (JWNLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("lhdgriver says : dictionary can't get instance!");
			return 1;
		}
		
		int num = 0;
		String query = null;
		buffered_query.append("insert into weight_wordnet.synset_word_gnum (word, synset_off, word_type) values ");
		while(iter.hasNext()) {
			num++;
			if (num % 2000 == 0) {
				query = buffered_query.toString();
				query = query.substring(0, query.length() - 1);
				stmt.execute(query);
				buffered_query.delete(0, buffered_query.length());
				buffered_query.append("insert into weight_wordnet.synset_word_gnum (word, synset_off, word_type) values ");
			}
			s = (Synset)iter.next();
			long offset = s.getOffset();
			Word s_words[] = s.getWords();
			for(Word temp_s : s_words) {
				 buffered_query.append("(\"" + temp_s.getLemma() + "\"," + offset + "," + word_type + "),");
			}
		}
		query = buffered_query.toString();
		query = query.substring(0, query.length() - 1);
		stmt.execute(query);

		return 0;
	}
	
	
	//step 1: fill word, synset_off in synset_word_gnum; word_type: 1 for noun, 2 for verb
	//step 2: fill synset_off_small, synset_off_big in related_synset
	//only consider verb and noun, and their inner and outer relations
	public int insertWordNetInfo() throws JWNLException {
///*		
		//step 1
		//for noun
		try {
			insertSynsetWordGnum(POS.NOUN);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("lhdgriver says : can't insert noun!");

		}
		//step 1
		//for verb
		try {
			insertSynsetWordGnum(POS.VERB);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("lhdgriver says : can't insert verb!");

		}
		System.out.println("fill synset_word_gnum!");
//*/		
		//step 2
		try {
			insertRelatedSynset();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("lhdgriver says : can't insert relationships within synsets");

		}
		
		return 0;
	}
}
